package com.cpi.cornpp.product.dao;

import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import com.cpi.cornpp.common.exception.CornPPException;
import com.cpi.cornpp.product.dto.ProductDTO;
import com.cpi.cornpp.product.exception.ProductException;
import com.cs.common.pool.DBDAO;
import com.cs.common.pool.exception.PoolException;

public class ProductDAO extends DBDAO {

	private static final String DATASOURCE = "cornpp";//$NON-NLS-1$

	/** SQL List Period */
	private static final String SQL_LIST_PRODUCT = "SELECT item_id,description FROM vw_items "; //$NON-NLS-1$

	private static final String SQL_PRODUCT_BY_ID = "SELECT item_id,description FROM vw_items where item_id = ?"; //$NON-NLS-1$

	private static final String SQL_WHERE_DESCRIPTION = " where DESCRIPTION like ? ";

	private static final String SQL_OR_ID = " OR item_id like ? ";

	private static final String SQL_LIST_CURRENT_FORESCATED_PRODUCTS = "SELECT DISTINCT it.item_id,it.description FROM vw_sales_forecasts sf, months_periods mp, futures_periods fp, vw_items it WHERE sf.month_period_id = mp.month_period_id AND fp.start_date <= mp.start_date AND mp.end_date <= fp.end_date AND contracted > 0 AND sf.item_id=it.item_id AND sf.customer_id = ? ";
//								AND mp.end_date > GETDATE()"; Agregar esta linea en prod
	private static final String SQL_PURCHASE_INSTRUCTION_ITEM_DETAIL = "select vw_items.item_id, vw_items.description from purchases_instructions_items_details INNER JOIN vw_items on purchases_instructions_items_details.item_id =  vw_items.item_id where purchase_instruction_detail_id = ?";
	
	public List getPurchaseIntructionItemDetails(int purchaseIntructionDetailId)throws ProductException{
		PreparedStatement preparedStatement = null;
		ResultSet resultSet  = null;
		List productDTOs = null;
		ProductDTO productDTO = null;
		try{
			productDTOs = new ArrayList();
			preparedStatement = prepareSQL(SQL_PURCHASE_INSTRUCTION_ITEM_DETAIL, dsName);
			preparedStatement.setInt(1, purchaseIntructionDetailId);
			resultSet = preparedStatement.executeQuery();
			while(resultSet.next()){
				productDTO = new ProductDTO();
				productDTO.setProductId(resultSet.getString(1));
				productDTO.setDescription(resultSet.getString(2));
				productDTOs.add(productDTO);
			}
		} catch (PoolException poolException) {
			ProductException productException = null;
			poolException.printStackTrace(System.out);
			productException = new ProductException(poolException,
					CornPPException.LAYER_DAO, CornPPException.ACTION_SELECT);
			throw productException;
		} catch (SQLException sqlException) {
			ProductException productException = null;
			sqlException.printStackTrace(System.out);
			productException = new ProductException(sqlException,
					CornPPException.LAYER_DAO, CornPPException.ACTION_SELECT);
			throw productException;
		} finally {
			try {
				closeConnection(preparedStatement.getConnection());
			} catch (SQLException sqle) {}
		}
		return productDTOs;
	}
	
	public ProductDTO getProductById(ProductDTO cusDTO)
			throws ProductException {
		ProductDTO productDTO = null;
		PreparedStatement pstm = null;
		ResultSet rs = null;

		try {

			if (cusDTO != null) {
				pstm = prepareSQL(SQL_PRODUCT_BY_ID, dsName);

				pstm.setString(1, cusDTO.getProductId());

				rs = executeQuery(pstm);
				if(rs.next()){
					productDTO = new ProductDTO();
					productDTO .setProductId(rs.getString(1));
					productDTO .setDescription(rs.getString(2));
				}
			}
		} catch (PoolException poolException) {
			ProductException productException = null;
			poolException.printStackTrace(System.out);
			productException = new ProductException(poolException,
					CornPPException.LAYER_DAO, CornPPException.ACTION_SELECT);
			throw productException;
		} catch (SQLException sqlException) {
			ProductException productException = null;
			sqlException.printStackTrace(System.out);
			productException = new ProductException(sqlException,
					CornPPException.LAYER_DAO, CornPPException.ACTION_SELECT);
			throw productException;
		} finally {
			try {

				closeConnection(pstm.getConnection());

			} catch (SQLException sqle) {

			}
		}

		return productDTO;
	}

	/**
	 * Obtener una lista de productos basado en el filtro descripcion
	 */
	public List list(ProductDTO entity) throws Exception {
		ProductDTO resultDTO = null;
		ArrayList arrayResult = null;
		PreparedStatement preparedStatement = null;
		StringBuffer sql = null;
		try {
			arrayResult = new ArrayList(0);
			sql = new StringBuffer(0);
			sql.append(SQL_LIST_PRODUCT);
			sql.append(SQL_WHERE_DESCRIPTION);
			sql.append(SQL_OR_ID);
			preparedStatement = prepareSQL(sql.toString(), DATASOURCE);

			preparedStatement.setString(1, entity.getDescription());
			preparedStatement.setString(2, entity.getDescription());

			ResultSet rst = executeQuery(preparedStatement);
			while (rst.next()) {
				resultDTO = new ProductDTO();
				resultDTO.setProductId(rst.getString(1));
				resultDTO.setDescription(rst.getString(2));

				arrayResult.add(resultDTO);
			}
		} catch (SQLException sqlException) {
			ProductException productException = null;
			sqlException.printStackTrace(System.out);
			productException = new ProductException(sqlException,
					CornPPException.LAYER_DAO, CornPPException.ACTION_LISTS);
			throw productException;
		} finally {
			try {
				closeConnection(preparedStatement.getConnection());
			} catch (Exception exception) {
			}
		}
		return arrayResult;
	}
	
	public List getCurrentForescastedProductList(String clientId) throws Exception {
		List productList = null;
		ProductDTO product = null;
		PreparedStatement preparedStatement = null;
		StringBuffer sql = null;
		try{
			sql = new StringBuffer(SQL_LIST_CURRENT_FORESCATED_PRODUCTS);
			preparedStatement = prepareSQL(sql.toString(), DATASOURCE);
			preparedStatement.setString(1, clientId);
			ResultSet rst = executeQuery(preparedStatement);
			productList = new ArrayList(0);
			while (rst.next()) {
				product = new ProductDTO();
				product.setProductId(rst.getString(1));
				product.setDescription(rst.getString(2));
				productList.add(product);
			}
		} catch (SQLException sqlException) {
			ProductException productException = null;
			sqlException.printStackTrace(System.out);
			productException = new ProductException(sqlException, CornPPException.LAYER_DAO, CornPPException.ACTION_LISTS);
			throw productException;
		} finally {
			try {
				closeConnection(preparedStatement.getConnection());
			} catch (Exception exception) {
			}
		}
		return productList;
	}
	

}
